﻿"""
根据上传的不加系数数据与标准件进行比对，计算校准系数

"""
import pyodbc
import configparser
import os
import shutil
from datetime import datetime
import pandas as pd
from sqlalchemy import create_engine
from sortingdataprocess import get_item_code, get_standpart_data, get_test_data, cal_coef
from setiniconfig import *
from getcobspec import *

# 创建SQL server sqlalchemy数据库连接

pd.set_option('display.max_columns', None)

ini_file_init = configparser.ConfigParser()
ini_file_download = configparser.ConfigParser()


def config_coef(form2_infos, cnxn, conn, engine, logger, flag):
    flash_result = {}
    coefconf_path = None
    lotID2 = None
    upload_path = None
    # 创建游标
    cursor = cnxn.cursor()
    cursor.fast_executemany = True
    # 创建游标
    cursor1 = conn.cursor()
    cursor1.fast_executemany = True
    # 查询web相关session是否为None
    item_code = get_item_code(form2_infos['MoID'])
    if item_code:
        std_data = get_standpart_data(item_code)
    else:
        std_data = pd.DataFrame()
    # 调用sortingdataprocess.py 中的get_test_data读取测试数据
    test_data = get_test_data(form2_infos['temp_path'], form2_infos['MachineID'], logger)
    len1 = len(test_data)
    # print(test_data, std_data)

    if std_data.empty or test_data.empty:
        logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--"
                    f"标准件数据为{not std_data.empty},测试数据为{not test_data.empty} -- False表示该数据有误！")
        flash_result[0] = f"标准件数据为{not std_data.empty},测试数据为{not test_data.empty}"
    elif form2_infos['MoID'].startswith('5101-') and len1 < 3:
        logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试数据少于3条!")
        flash_result[1] = "请使用3个或3个以上标准灯进行测试！！"
    else:
        # 删除电流为NA和0，去除Seclect列
        test_data = test_data[test_data['Current_mA'] != 0]
        test_data.dropna(subset=['Current_mA'], inplace=True)
        test_data.drop(columns=['Select'], axis=1, inplace=True)
        coef_data = cal_coef(std_data, test_data)
        if type(coef_data) == pd.DataFrame and coef_data.empty:
            logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试数据与标准件数据编号不对应!")
            flash_result[2] = "测试数据与标准件数据编号不对应"
        elif type(coef_data)!=pd.DataFrame and not coef_data:
            logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试数据无效!")
            flash_result[2] = "机台启动失败,光谱图数据无效，请发起设备维修申请"
        elif not coef_data.empty and coef_data.loc['校准系数', 'dI'] > 0.05:
            logger.warn(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--测试电流与标准件电流偏差超过5%!")
            flash_result[3] = "测试电流与标准件电流偏差超过5%"
        else:
            # 查询工单+lotID组合下的LOTID2是否存在，存在+1，不存在初始化为1
            cursor.execute(
                f"SELECT MAX(LOTID2) FROM Gencoef_Info1 "
                f"WHERE MOID='{form2_infos['MoID']}' AND LotID='{form2_infos['LotID']}'")
            rows = cursor.fetchall()
            if rows[0][0] is None:
                lotID2 = 1
            else:
                lotID2 = rows[0][0] + 1

            # 添加LOTID2到列表
            form2_infos['LotID2'] = lotID2

            # 创建上传与下载目录并添加到列表
            uploadsDir = os.path.join('uploads', 'rawdata', str(datetime.now().year), str(datetime.now().month))
            if not os.path.exists(uploadsDir):
                os.makedirs(uploadsDir)
            downloadsDir = os.path.join('downloads', 'coefconf', str(datetime.now().year), str(datetime.now().month))
            if not os.path.exists(downloadsDir):
                os.makedirs(downloadsDir)

            # 修改上传文件名并保存
            ext = os.path.splitext(form2_infos['temp_path'])[1]
            upnew_filename = 'raw-' + form2_infos['MoID'] + '-' + str(form2_infos['LotID']) + '-' + str(lotID2) + ext
            upload_path = os.path.join(uploadsDir, upnew_filename)
            form2_infos['UploadPath'] = upload_path
            shutil.copy(form2_infos['temp_path'], form2_infos['UploadPath'])

            # 获取标准件的测试时间
            get_time = f"SELECT StandardPartNO as TestNO,CONVERT(char,TestTime,120) as StandardTime " \
                       f"FROM StandardPartData WHERE " \
                       f"StandardPartID='{form2_infos['StandardPartID']}' AND  DataStatus=1"
            time_df = pd.read_sql(get_time, engine)
            test_data = pd.merge(time_df, test_data, on='TestNO')
            # 获取测试数据
            pars = ['MoID', 'StandardPartID', 'MachineID', 'UserID', 'LotID', 'LotID2']
            for i in pars:
                test_data[i] = form2_infos[i]
            try:
                # 数据写入数据库
                test_data.to_sql('Gencoef_Data1', engine, if_exists='append', index=False)
            except Exception as e:
                logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--校准数据存储错误,{e}")
                flash_result[4] = f"校准数据存储错误！,问题描述：{e}"
            else:
                if '校准系数' not in list(coef_data.index):
                    logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单不存在'校准系数'行")
                    flash_result[5] = "校准系数获取失败！"
                else:
                    dict_coef_data = coef_data.loc['校准系数'].to_dict()
                    if not flag and (abs(dict_coef_data['dx']) > 0.01 or abs(dict_coef_data['dy']) > 0.1):
                        logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--机差超过1%")
                        flash_result[6] = "检查标准灯背面喷码，确认使用的是对应的标准灯！！<br>确认无误后返回继续校准"
                    else:
                        # 查询COF中是否存在之前订单、机台一样的数据
                        sql_select = f"SELECT max(StandardPartNO) FROM COF WHERE MoID='{form2_infos['MoID']}' and CF='校准系数' and " \
                                     f"MachineID='{form2_infos['MachineID']}'"
                        row = cursor1.execute(sql_select).fetchall()
                        if row[0][0] is None:
                            No = 1
                        else:
                            No = int(row[0][0]) + 1
                        sql_insert = f"INSERT INTO COF (MoID,CF,ITEM_CODE,StandardPartNO," \
                                     f"dU,dx,dy,dRa,dR9,dlm,dI,MachineID,Updatetime,LotID) " \
                                     f"values ('{form2_infos['MoID']}','校准系数','{item_code}','{No}'," \
                                     f"{dict_coef_data['dU']},{dict_coef_data['dx']},{dict_coef_data['dy']}," \
                                     f"{dict_coef_data['dRa']},{dict_coef_data['dR9']},{dict_coef_data['dlm']}," \
                                     f"{dict_coef_data['dI']},'{form2_infos['MachineID']}','{datetime.now()}'," \
                                     f"'{str(form2_infos['LotID']) + '-' + str(form2_infos['LotID2'])}')"
                        try:
                            cursor1.execute(sql_insert)
                            conn.commit()
                        except Exception as e:
                            logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--校准系数存储错误,{e}")
                            flash_result[7] = f"校准系数-存储错误！问题描述：{e}"
                        else:
                            # 读取下发的配置文件
                            ini_file_read(form2_infos['InitPath'])

                            set_cal_factor(dict_coef_data)
                            # 将修改后的配置文件另存为
                            down_filename = '生成-' + form2_infos['MoID'] + '-' + str(form2_infos['LotID']) + '-' + str(
                                lotID2) + '.ini'
                            coefconf_path = os.path.join(downloadsDir, down_filename)
                            form2_infos['DownloadPath'] = coefconf_path
                            ini_file_save(coefconf_path)

                            # 列表插入当前时间
                            form2_infos['UpdateTime'] = str(datetime.now())
                            # 删除字典temp_path及临时文件
                            os.remove(form2_infos.pop('temp_path'))
                            # print(form2_infos)
                            # 遍历字典
                            kvs = [(k, v) for k, v in form2_infos.items() if v is not None]
                            # 创建sql语句
                            sql_data = f"INSERT INTO Gencoef_Info1({','.join(i[0] for i in kvs)}) " \
                                       f"VALUES ({','.join(repr(i[1]) for i in kvs)})"
                            try:
                                cursor.execute(sql_data)
                                cnxn.commit()
                            except Exception as e:
                                logger.error(f"{form2_infos['MachineID']}号设备，{form2_infos['MoID']}工单--校准信息存储错误,{e}")
                                flash_result[8] = f"校准信息-存储错误！问题描述：{e}"
                            else:
                                flash_result['成功'] = '中间系数计算成功'
                                logger.info(
                                    f"{form2_infos['MachineID']}号，{form2_infos['MoID']}工单--中间系数计算成功-{form2_infos['InitPath']}")
    return flash_result, coefconf_path, lotID2, upload_path
